Defining Metrics on MS SQL Server

Since MS SQL does not support an aggregate function on an expression containing an aggregate or a sub-query, such metrics will need to be defined differently in MS SQL.

Any metric that contains in its formula an aggregate function (ex. SUM, AVG, MAX, MIN, COUNT) and has a Report: Aggregate value other than 'NONE' will trigger an error on MS SQL and will require redesigning the metric's formula.

The following example shows how to convert a metric formula using two levels of aggregation.

Example

On Oracle:

Report: Aggregate As	SUM
Collect: Table	bl (Buildings table)
Collect: Formula	area_ls_negotiated/(SELECT COUNT(em_id) FROM em)
Metric Granularity Assignments	by Building

On MS SQL:

Report: Aggregate As	NONE
Collect: Table	bl (Buildings table)
Collect: Formula	(SELECT SUM(area_ls_negotiated) FROM bl)/(SELECT COUNT(em_id) FROM em)
Metric Granularity Assignments	by Building